#!/usr/local/bin/python
# -*- coding: utf-8 -*-


from sqlalchemy import Column, Integer, Float, and_, Text, Date
from sqlalchemy.orm import declarative_base

import init
import toolkit
from init import session


Base = declarative_base()

class BalanceSheetMonthlyAlchemy(Base):
    '''
    类功能：按照月份统计资产负债表的合计数据
    '''
    __tablename__ = "balance_sheet_month"

    id = Column(Integer, primary_key=True, autoincrement=True, comment="数据表id值")
    date_time = Column(Date, comment="数字格式的年份和月份时间")
    year = Column(Integer, comment="数据归属年份")
    month = Column(Integer, comment="数据归属月份")
    data_type = Column(Text, comment="标记属于[资产]或[权属]")
    start_value = Column(Float, comment="月初金额")
    end_value = Column(Float, comment="月末金额")

    def __init__(self,
                 year=2022,
                 month=1,
                 dataType="",
                 startValue=0.0,
                 endValue=0.0):

        self.date_time = toolkit.setTargetDate(year, month)
        self.year = year
        self.month = month
        self.data_type = dataType
        self.start_value = startValue
        self.end_value = endValue

    def AddBalanceSheetItem(self):
        '''
            函数功能：添加一条数据
            :return:
        '''
        session.add(self)
        session.commit()


    def UpdateBalanceSheetItem(self):
        '''
        函数功能；更新一条数据,暂未实现
        :return:
        '''

    def SearchBalanceSheetByMonth(self, year, month):
        '''
        函数功能：以时间和资产负债表科目为条件进行查询
        :param year:
        :param month:
        :return:
        '''

        balanceSheetInfoMonthly = session.query(BalanceSheetMonthlyAlchemy).filter(and_(BalanceSheetMonthlyAlchemy.year == year,
                                                                        BalanceSheetMonthlyAlchemy.month == month
                                                                          )).all()

        return balanceSheetInfoMonthly

    def SearchBalanceSheetPropertyByYear(self, year):
        '''
        函数功能：查询指定年份各月的收支情况
        :param year:
        :return:
        '''
        balanceSheetInfo = session.query(BalanceSheetMonthlyAlchemy)\
                                     .filter(BalanceSheetMonthlyAlchemy.year==year)  \
                                     .filter(BalanceSheetMonthlyAlchemy.data_type=="property")  \
                                     .all()
        return balanceSheetInfo


    def ShowData(self):
        '''
        函数功能：展示读取出来的数据
        :return:
        '''
        pass

    def DelBalanceSheetByMonth(self, year, month):
        '''
        函数功能，删除指定月份的张博数据
        :param year:
        :param month:
        :return:
        '''

        deleteBalanceSheetInfo = session.query(BalanceSheetMonthlyAlchemy).filter(and_(BalanceSheetMonthlyAlchemy.year==year,
                                                                     BalanceSheetMonthlyAlchemy.month==month))
        deleteBalanceSheetInfo.delete()
        session.commit()


    def UpdateAllDatetimeDataTemp(self):
        '''
        函数功能：临时更新收支表数据,用于填充date_time字段的数据
        :return:
        '''

        balanceSheetMonthlyAlchemyList = session.query(BalanceSheetMonthlyAlchemy).filter().all()

        for i in range(len(balanceSheetMonthlyAlchemyList)):

            balanceSheetMonthlyAlchemyItem = balanceSheetMonthlyAlchemyList[i]
            balanceSheetMonthlyAlchemyItem .date_time = toolkit.setTargetDate(balanceSheetMonthlyAlchemyItem .year,
                                                                      balanceSheetMonthlyAlchemyItem .month)

        session.commit()
        return

    def GetLatestPropertyValueByYear(self, year):
        '''
        函数功能；获得最新的资产数据
        :return:
        '''

        latestPropertyValueItem = session.query(BalanceSheetMonthlyAlchemy).filter(and_(BalanceSheetMonthlyAlchemy.year==year,
                                                                                       BalanceSheetMonthlyAlchemy.data_type=="property"))\
                                                                          .order_by(BalanceSheetMonthlyAlchemy.date_time.desc()) \
                                                                          .first()

        if latestPropertyValueItem==None:
            return None

        return  latestPropertyValueItem.end_value


def main():
    balanceSheetMonthlyAlchemy = BalanceSheetMonthlyAlchemy()
    balanceSheetMonthlyAlchemy.UpdateAllDatetimeDataTemp()


    '''
    balanceSheetMonthlyAlchemy = BalanceSheetMonthlyAlchemy(year=2022,
                                          month=1,
                                          dataType="property",
                                          startValue=110.11,
                                          endValue=120.22
                                          )

    balanceSheetMonthlyAlchemy.AddBalanceSheetItem()
    '''
#创建数据库表
#BalanceSheetMonthlyAlchemy.metadata.create_all(init.engine)
if __name__ == '__main__':
    main()